package com.dckeji.shiplock.webapp.dao.impl;

import com.dckeji.shiplock.webapp.dao.IOwnerDao;
import com.dckeji.shiplock.webapp.pojo.Crew;
import com.dckeji.shiplock.webapp.pojo.Owner;
import com.dckeji.shiplock.webapp.pojo.Route;
import com.dckeji.shiplock.webapp.pojo.UserInfo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

/**
 * Created with IntelliJ IDEA.
 * User: TianWei
 * Date: 2017/8/11
 * Time: 10:11
 */
@Repository
public class OwnerDaoImpl implements IOwnerDao {

    @Autowired
    JdbcTemplate jdbcTemplate;

    @Override
    public int ownerAuthentication(Owner owner) {
        String sql = "INSERT INTO `owner` (shipNo,ownerPhone,ownerName,regDate,ownerPwd,ownerOpenId) VALUES(?,?,?,?,?,?)";

        return jdbcTemplate.update(sql, new Object[]{owner.getShipNo(), owner.getPhone(), owner.getName(), owner.getRegDate(),
                owner.getPwd(), owner.getOpenid()
        });

    }


    @Override
    public Owner queryOwnerInfo(UserInfo userInfo) {
        String sql = "select * from `owner`  where shipNo =?";
        List<Owner> owner = null;
        try {
            owner = jdbcTemplate.query(sql, new BeanPropertyRowMapper<Owner>(Owner.class), new Object[]{userInfo.getShipNo()});
        } catch (Exception e) {
            return null;
        }
        if (owner != null && owner.size() > 0) {
            return owner.get(0);
        }

        return null;
    }

    @Override
    public int addCrew(Crew crew) {
        String sql = "INSERT INTO crew(crewName,ownerId,crewPhone) VALUES (?,?,?)";

        return jdbcTemplate.update(sql, new Object[]{crew.getName(), crew.getOwnerId(), crew.getPhone()});
    }

    @Override
    public List<Crew> queryCrewInfos(Integer ownerId) {
        String sql = "select * from crew where ownerId=? and crewStatus=0";

        return jdbcTemplate.query(sql, new RowMapper<Crew>() {
            @Override
            public Crew mapRow(ResultSet resultSet, int i) throws SQLException {
                Crew c = new Crew();
                c.setPhone(resultSet.getString("crewPhone"));
                c.setOpenid(resultSet.getString("crewOpenId"));
                c.setName(resultSet.getString("crewName"));
                c.setOwnerId(resultSet.getInt("ownerId"));
                c.setCrewId(resultSet.getInt("crewId"));
                return c;
            }
        }, ownerId);
    }

    @Override
    public int deleteCrew(Integer crewId) {
        String sql = "delete from crew  where crewId =?";


        return jdbcTemplate.update(sql, new Object[]{crewId});
    }

    @Override
    public int updateCrew(Crew crew) {
        String sql = "update crew set crewName=?,crewPhone=? where crewId=?";
        return jdbcTemplate.update(sql, new Object[]{crew.getName(), crew.getPhone(), crew.getCrewId()});
    }

    @Override
    public Owner queryOwnerInfoByShipNo(String shipNo) {
        String sql = "select * from `owner`  where shipNo=? limit 1";
        List<Owner> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<Owner>(Owner.class), new Object[]{shipNo});
        if (null != query && query.size() > 0) {
            return query.get(0);
        }
        return null;
    }

    @Override
    public int updateCrewPwd(UserInfo userInfo) {
        String sql = "update crew set crewPwd=? where crewPwd=? and crewOpenId=?";


        return jdbcTemplate.update(sql, new Object[]{userInfo.getPwd(), userInfo.getOldPwd(), userInfo.getOpenid()});
    }

    @Override
    public int updateOwnerpwd(UserInfo userInfo) {

        String sql = "update `owner` set ownerPwd =? where ownerPwd=? and ownerOpenId=? ";
        return jdbcTemplate.update(sql, userInfo.getPwd(), userInfo.getOldPwd(), userInfo.getOpenid());
    }

    @Override
    public Route queryRouteByOpenId(String openId) {
        String sql = "select * from route where openId=?";
        List<Route> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<Route>(Route.class), new Object[]{openId});
        if (null != query && query.size() > 0) {
            return query.get(0);
        }
        return null;
    }

    @Override
    public int crewAuth(Crew crew) {
        String sql = "UPDATE crew set crewOpenId=?,crewStatus=1 where crewPhone=?";

        return jdbcTemplate.update(sql, new Object[]{crew.getOpenid(), crew.getPhone()});
    }

    @Override
    public int addRoute(Route route) {

        String sql = "INSERT INTO route(routeType,openId,shipNo) values(?,?,?)";

        return jdbcTemplate.update(sql, new Object[]{route.getRouteType(), route.getOpenId(), route.getShipNo()});
    }

    @Override
    public Crew queryCrewByPhone(Crew crew) {
        String sql = "select c.crewId,c.crewName,c.crewPhone,c.crewStatus,c.crewOpenId from crew  c INNER JOIN `owner` o on c.ownerId = o.ownerId where  shipNo = ?  and crewPhone=?  and crewStatus=0";

        return jdbcTemplate.queryForObject(sql, new RowMapper<Crew>() {
            @Override
            public Crew mapRow(ResultSet resultSet, int i) throws SQLException {
                Crew crew1 = new Crew();
                crew1.setCrewId(resultSet.getInt("crewId"));
                crew1.setName(resultSet.getString("crewName"));
                crew1.setOpenid(resultSet.getString("crewOpenId"));
                crew1.setPhone(resultSet.getString("crewPhone"));
                crew1.setOwnerId(resultSet.getInt("ownerId"));
                return crew1;
            }
        }, new Object[]{crew.getShipNo(), crew.getPhone()});
    }

    @Override
    public Crew queryCrewByOwner(UserInfo userInfo) {
        StringBuffer sql = new StringBuffer();

        sql.append("SELECT c.crewId,c.crewName,c.crewPhone,c.crewStatus,c.crewOpenId FROM crew c")
                .append(" INNER JOIN `owner` o ON c.ownerId = o.ownerId")
                .append(" WHERE c.ownerId=? ");

        List<Crew> query = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<Crew>(Crew.class), userInfo.getOwnerId());
        if (null != query && query.size() > 0) {
            return query.get(0);
        }
        return null;
    }

    @Override
    public Crew queryCrewByOpenId(String openId) {

        String sql = "select * from crew  where crewOpenId=?";
        List<Crew> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<Crew>(Crew.class), new Object[]{openId});
        if (null != query && query.size() > 0) {
            return query.get(0);
        }

        return null;
    }

    @Override
    public List<Route> route(String openid) {
        String sql = " select * from route where openId=? ";

        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<Route>(Route.class), openid);
    }


}
